package com.jiyuren.spring.utils;

import com.jiyuren.spring.bean.WebSite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/2/8.
 */
public class DBConn {
    final static String driver = "org.sqlite.JDBC";
    final static String url = "jdbc:sqlite:D:/website.db";
    Connection conn = null;
    private static volatile DBConn mInstance;


    public static DBConn getInstance() {
        if (mInstance == null) {
            synchronized (DBConn.class) {
                if (null == mInstance) {
                    mInstance = new DBConn();
                }
            }
        }
        return mInstance;
    }

    private DBConn() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void reConn() {
        try {
            if (null == conn || conn.isClosed()) {
                Class.forName(driver);
                conn = DriverManager.getConnection(url);
            }
        } catch (Exception e) {
            e.fillInStackTrace();
        }
    }

    public Connection getConn() {
        reConn();
        initTable();
        return conn;
    }

    private void initTable() {
        try {
            Statement stat = conn.createStatement();
            stat.execute("create table if not exists website " +
                    "(id integer primary key AUTOINCREMENT, url varchar(1000),title_name varchar(255),"
                    + "access_time_interval integer(10),time_out integer(10),"
                    + "access_time varchar(255),is_del integer(1),"
                    + "is_run integer(1),access_times integer(10))");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public List<WebSite> getList() {
        List<WebSite> list = new ArrayList<WebSite>();
        int pos = 0;
        Statement stat = null;
        ResultSet rs = null;
        try {
            String sql = "select * from website where is_del = 0";
            stat = getConn().createStatement();
            rs = stat.executeQuery(sql);
            WebSite site = null;
            while (rs.next()) {
                site = new WebSite();
                String url = rs.getString("url");
                String titleName = rs.getString("title_name");
                int accessTimeInterval = rs.getInt("access_time_interval");
                String accessTime = rs.getString("access_time");
                int accessTimes = rs.getInt("access_times");
                int isDel = rs.getInt("is_del");
                int isRun = rs.getInt("is_run");
                int timeOut = rs.getInt("time_out");
                int id = rs.getInt("id");
                site.setId(id);
                site.setTime_out(timeOut);
                site.setIs_run(isRun);
                site.setIs_del(isDel);
                site.setAccess_times(accessTimes);
                site.setAccess_time(accessTime);
                site.setAccess_time_interval(accessTimeInterval);
                site.setTitle_name(titleName);
                site.setUrl(url);
                site.setIndex(pos);
                site.setIs_green(isRun);
                list.add(site);
                pos += 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != stat) {
                    stat.close();
                }
                getConn().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DBConn.getInstance().getConn();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select * from website where id < 20");
            while (rs.next()) {
                String titleName = rs.getString("title_name");
                System.out.println(titleName);
            }
            rs.close();
            stat.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
